<?

/**
* Global fetchers functions
*
* @author Adesanto Asman
* @version 1.0
* @license Free
* @package CGFetchers
*
*/

class CGFetchers{

  /**
  * Fetch if this model id is serNumber active
  *
  * @param  string  $Db
  * @param  integer $modelId
  * @param  string  $srcDb
  * @param  integer $isSerNumber
  *
  */
  function isModelSerNumber(&$Db,
                            &$modelId,
                             $srcDb,
                            &$isSerNumber){

    define('_IS_ACTIVE'     , 1);

    $query  = "SELECT SER_NUMBER
               FROM ".$srcDb."
               WHERE  MODEL_ID='".$modelId."' ";

    $Db->execFetchQuery($query,
                        $nrows,
                        $result);

    if($result["SER_NUMBER"][0] == _IS_ACTIVE){

      $isSerNumber = true;
    }else{

      $isSerNumber = false;
    }
  }

  /**
   * last number of mutation
   *
   * @param Database  $Db
   * @param string    $refId
   * @param string    $srcDb
   * @param integer   $lastMutNumber
   */
  function fetchLastMutationNo(&$Db,
                               &$refId,
                                $srcDb,
                               &$lastMutNumber){

    $query  = "SELECT NO
               FROM ".$srcDb."
               WHERE  REFERENCE='".$refId."'
               ORDER BY NO DESC  ";

    $Db->execFetchQuery($query,
                        $nrows,
                        $result);

    if($nrows > 0){

      $lastMutNumber = $result["NO"][0] + 1;
    }else{

      $lastMutNumber = 1;
    }
  }

  /**
   * Tx Type Code by Name
   *
   * @param Database  $Db
   * @param string    $txName
   * @param string    $srcDb
   * @param string    $txCode
   */
  function fetchTxTypeCode(&$Db,
                           &$txName,
                            $srcDb,
                           &$txCode){

    $query  = "SELECT CASH_BANK_TX_CODE
               FROM ".$srcDb."
               WHERE  CASH_BANK_TX_NAME='".$txName."' ";

    $Db->execFetchQuery($query,
                        $nrows,
                        $result);

    $txCode = $result["CASH_BANK_TX_CODE"][0];
  }

  /**
   * Purchased qty in warehouse balance
   * check if we have quantity match between
   * purchasing note and the warehouse received note
   *
   * @param Database  $Db
   * @param string    $srcNota
   * @param string    $modelId
   * @param integer   $qty
   * @param Composer  $SourceDb
   * @param array     $match
   */
  function fetchWhsPchQtyStatus(&$Db,
                                &$srcNota,
                                &$modelId,
                                &$qty,
                                &$SourceDb,
                                &$match){

    $query  = "SELECT D.*
               FROM ".$SourceDb->fetchValue("srcDbSnDtl")." D,
                    ".$SourceDb->fetchValue("srcDbSnHdr")." H
               WHERE  D.MODEL_ID='".$modelId."'
                      AND H.NOTA_SRC='".$srcNota."'
                      AND D.QTY=".$qty."
                      AND H.JOB_ID=D.JOB_ID
                      AND H.STATUS='"._ACTIVE."' ";

    $Db->execFetchQuery($query,
                        $nrows,
                        $result);

    if($nrows > 0){

      $match["isExists"] = true;

    }else{

      $match["isExists"] = false;
    }

    if ($qty == $result["QTY"][0]) {

      $match["isMatch"] = true;
    }else{

      $match["isMatch"] = false;
    }
  }

  /**
   * list od valid user
   *
   * @param Database  $Db
   * @param string    $srcDb
   * @param integer   $nUser
   * @param array     $lstUser
   */
  function fetchLstUser(&$Db,
                         $srcDb,
                        &$nUser,
                        &$lstUser){

    $query	= "SELECT *
               FROM ".$srcDb."
               WHERE	STATUS=1
               ORDER BY USERNAME ASC";

    $Db->execFetchQuery($query,
                        $nUser,
                        $lstUser);
  }

  /**
   * list of cash bank type
   *
   * @param Database  $Db
   * @param string    $srcDb
   * @param integer   $nCbType
   * @param array     $lstCbType
   */
  function fetchLstCbType(&$Db,
                           $srcDb,
                          &$nCbType,
                          &$lstCbType){

    $query	= "SELECT CODE,
                      TYPE
               FROM ".$srcDb."
               ORDER BY CODE ASC";

    $Db->execFetchQuery($query,
                        $nCbType,
                        $lstCbType);
  }

  /**
  * Fetch list of currency
  *
  * @param  Database  $Db
  * @param  string    $srcDb
  * @param  integer   $nCurrency
  * @param  array     $lstCurrency
  *
  */
  function fetchLstCurrency(&$Db,
                             $srcDb,
                            &$nCurrency,
                            &$lstCurrency){

    $query  = "SELECT CRR_SYMBOL ".
              "FROM ".$srcDb." ".
              "ORDER BY CRR_SYMBOL ASC ";

    $Db->execFetchQuery($query,
                        $nCurrency,
                        $result);

    $lstCurrency = $result["CRR_SYMBOL"];
  }

  /**
   * fetch if the SN is valid
   *
   * @param Database  $Db
   * @param string    $chkSn
   * @param string    $srcDb
   * @param boolean   $isValid
   */
  function fetchIsSnValid(&$Db,
                          &$chkSn,
                           $srcDb,
                          &$isValid){

    $query  = "SELECT *
               FROM ".$srcDb."
               WHERE  SN='".$chkSn."' ";

    $Db->execFetchQuery($query,
                        $nrows,
                        $result);

    if ($nrows > 0) {

      $isValid = false;
    }else{

      $isValid = true;
    }
  }

  /**
   * check if SN is exist
   *
   * @param Database  $Db
   * @param string    $SN
   * @param string    $srcDb
   * @param boolean   $isExist
   */
  function fetchIsSnExists(&$Db,
                           &$SN,
                            $srcDb,
                           &$isExist){

    $query	= "SELECT *
               FROM ".$srcDb."
               WHERE	SN='".$SN."' ";

    $Db->execFetchQuery($query,
                        $nrows,
                        $result);

    if($nrows > 0){

      $isExist = true;
    }else{

      $isExist = false;
    }
  }

  /**
   * list of model in warehouse
   *
   * @param Database  $Db
   * @param Composer  $SourceDb
   * @param integer   $nModel
   * @param array     $lstModel
   */
  function fetchWhsModel(&$Db,
                         &$SourceDb,
                         &$nModel,
                         &$lstModel){

    $query	= "SELECT DISTINCT
                      D.MODEL_ID,
                      M.MODEL,
                      M.UNIT
               FROM ".$SourceDb->fetchValue("srcDbWhs")."     D,
                    ".$SourceDb->fetchValue("srcDbModel")."   M
               WHERE  M.MODEL_ID=D.MODEL_ID
               ORDER BY M.MODEL ASC";

    $Db->execFetchQuery($query,
                        $nModel,
                        $lstModel);
  }

  /**
  * Fetch if this model id is serNumber active
  *
  * @param  string  $Db
  * @param  string  $model
  * @param  integer $modelId
  * @param  string  $srcDb
  * @param  boolean $isSerNumber
  *
  */
  function fetchIsSerNumber(&$Db,
                            &$model,
                            &$modelId,
                             $srcDb,
                            &$isSerNumber){

    define('_IS_ACTIVE'     , 1);

    $query  = "SELECT SER_NUMBER
               FROM ".$srcDb."
               WHERE  MODEL='".$model."'
                      AND MODEL_ID='".$modelId."' ";

    $Db->execFetchQuery($query,
                        $nrows,
                        $result);

    if($result["SER_NUMBER"][0] == _IS_ACTIVE){

      $isSerNumber = true;
    }else{

      $isSerNumber = false;
    }
  }

  /**
   * lst number of model in this whs
   *
   * @param Database  $Db
   * @param integer   $modelId
   * @param string    $unit
   * @param string    $srcDb
   * @param integer   $lstNo
   */
  function fetchLstNumberWhs(&$Db,
                             &$modelId,
                             &$unit,
                              $srcDb,
                             &$lstNo){

    $query	= "SELECT NO
               FROM ".$srcDb."
               WHERE  MODEL_ID='".$modelId."'
                      AND UNIT='".$unit."'
               ORDER BY NO DESC";

    $Db->execFetchQuery($query,
                        $nrows,
                        $result);

    if($nrows > 0){

      $lstNo = $result["NO"][0];
    }else{

      $lstNo = 1;
    }
  }

  /**
   * fetch warehouse balance
   *
   * @param Database  $Db
   * @param integer   $modelId
   * @param string    $unit
   * @param string    $srcDb
   * @param integer   $curBalance
   */
  function fetchWhsBalance(&$Db,
                           &$modelId,
                           &$unit,
                            $srcDb,
                           &$nrows,
                           &$curBalance){

    $query	= "SELECT *
               FROM ".$srcDb."
               WHERE	MODEL_ID='".$modelId."' ";

    $Db->execFetchQuery($query,
                        $nrows,
                        $result);

    if($nrows > 0){

      $curBalance = $result["QTY"][0];
    }else{

      $curBalance = 0;
    }
  }

  /**
   * list of warehouse
   *
   * @param unknown_type $Db
   * @param unknown_type $srcDb
   * @param unknown_type $nWhs
   * @param unknown_type $lstWhs
   */
  function fetchLstWhs(&$Db,
                        $srcDb,
                       &$nWhs,
                       &$lstWhs){

    $query	= "SELECT WHS_NAME
               FROM ".$srcDb."
               WHERE  STATUS='"._ACTIVE."'
               ORDER BY WHS_CODE ASC";

    $Db->execFetchQuery($query,
                        $nWhs,
                        $result);

    $lstWhs = $result["WHS_NAME"];
  }

  /**
   * make sure the SN is clean
   *
   * @param Database  $Db
   * @param string    $SN
   * @param integer   $type
   * @param string    $srcDb
   * @param booelan   $isClean
   */
  function fetchIsSnClean(&$Db,
                          &$SN,
                           $type,
                           $srcDb,
                          &$isClean){

    $query  = "SELECT TX_TYPE
               FROM ".$srcDb."
               WHERE  SN='".$SN."'
               ORDER BY NO DESC";

    $Db->execFetchQuery($query,
                        $nrows,
                        $result);

    if($result["TX_TYPE"][0] == $type){

      $isClean = true;
    }else{

      $isClean = false;
    }
  }

  /**
   * customer code
   *
   * @param Database  $Db
   * @param string    $customerName
   * @param string    $srcDb
   * @param integer   $customerCode
   */
  function fetchCustomerCode(&$Db,
                             &$customerName,
                              $srcDb,
                             &$customerCode) {

    $query  = "SELECT CUSTOMER_CODE
               FROM ".$srcDb."
               WHERE  CUSTOMER_NAME='".$customerName."' ";

    $Db->execFetchQuery($query,
                        $nrows,
                        $result);

    $customerCode = $result["CUSTOMER_CODE"][0];
  }

  /**
   * supplier code
   *
   * @param Database  $Db
   * @param string    $supplierName
   * @param string    $srcDb
   * @param integer   $supplierCode
   */
  function fetchSupplierCode(&$Db,
                             &$supplierName,
                              $srcDb,
                             &$supplierCode) {

    $query	= "SELECT SUPPLIER_CODE
               FROM ".$srcDb."
               WHERE	SUPPLIER_NAME='".$supplierName."' ";

    $Db->execFetchQuery($query,
                        $nrows,
                        $result);

    $supplierCode = $result["SUPPLIER_CODE"][0];
  }

  /**
   * list of customer
   *
   * @param Database $Db
   * @param string   $srcDb
   * @param array    $lstCustomer
   */
  function fetchCustomer(&$Db,
                          $srcDb,
                         &$lstCustomer) {

    $query  = "SELECT CUSTOMER_NAME
               FROM ".$srcDb."
               ORDER BY CUSTOMER_NAME ASC";

    $Db->execFetchQuery($query,
                        $nrows,
                        $result);

    $lstCustomer = $result["CUSTOMER_NAME"];
  }

  /**
   * list of suppliers
   *
   * @param Database $Db
   * @param string   $srcDb
   * @param array    $lstSupplier
   */
  function fetchSupplier(&$Db,
                          $srcDb,
                         &$lstSupplier) {

    $query  = "SELECT SUPPLIER_NAME
               FROM ".$srcDb."
               ORDER BY SUPPLIER_NAME ASC";

    $Db->execFetchQuery($query,
                        $nrows,
                        $result);

    $lstSupplier = $result["SUPPLIER_NAME"];
  }

  /**
   * model Id
   *
   * @param Database $Db
   * @param string   $modelName
   * @param string   $srcDb
   * @param integer  $modelId
   */
  function fetchModelId(&$Db,
                        &$modelName,
                         $srcDb,
                        &$modelId){

    $query  = "SELECT MODEL_ID
               FROM ".$srcDb."
               WHERE  MODEL='".$modelName."' ";

    $Db->execFetchQuery($query,
                        $nrows,
                        $result);

    $modelId = $result["MODEL_ID"][0];
  }

  /**
  * Fetch login
  * Check if the user, pass and company is exist and active
  * return true  if the user is active and exists
  *        false if null return from the db fetch
  *
  * @param  string    $COracle
  * @param  string    $user
  * @param  string    $pass
  * @param  string    $company
  * @param  string    $srcDb
  * @return booelan   true on success and false on failed
  *
  */
  function fetchLogin(&$COracle,
                      &$user,
                      &$pass,
                      &$company,
                      &$srcDb){

    $query  = "SELECT USERID ".
              "FROM ".$srcDb." ".
              "WHERE  USERID='".$user."' ".
                      "AND PASSWORD='".$pass."' ".
                      "AND COMPANY='".$company."' ";

    $COracle->execFetchQuery($query,
                             $nrows,
                             $result);
    if($nrows > 0){

      return true;
    }else{

      return false;
    }
  }

  /**
  * Fetch currently selected levelD
  *
  * @param  string    $COracle
  * @param  string    $srcDb
  * @param  string    $selected
  *
  */
  function fetchSelLevelD(&$COracle,
                          &$srcDb,
                          &$selected){

    $query  = "SELECT LEVELD ".
              "FROM ".$srcDb." ".
              "WHERE  STATUS='"._ACTIVE."' ";

    $COracle->execFetchQuery($query,
                             $nrows,
                             $result);

    $selected = $result["LEVELD"][0];
  }

  /**
  * fetch the last number of a table
  *
  * @param  string    $COracle
  * @param  string    $today_date
  * @param  string    $srcDb
  * @param  string    $lastNo
  *
  */
  function fetchLastNoByDate(&$COracle,
                             $today_date,
                             &$srcDb,
                             &$lastNo){

    $query  = "SELECT NO
               FROM ".$srcDb."
               WHERE  TR_DATE=TO_DATE('".$today_date."', 'dd-mm-YYYY')
               ORDER BY NO DESC ";

    $COracle->execFetchQuery($query,
                             $nrows,
                             $result);

    /**
    * Check if there is number in this date
    * YES: fetch it and increment
    * NO : Assign 1 as the first number
    *
    */
    if($nrows > 0){

      $lastNo = $result["NO"][0] + 1;
    }else{

      $lastNo = 1;
    }
  }

  /**
  * fetch the values data
  *
  * @param  string  $COracle
  * @param  string  $userId
  * @param  string  $srcValues
  * @param  string  $srcDbHdr
  * @param  string  $srcDbDtl
  * @param  string  $theResult
  *
  */
  function fetchUnserialize(&$COracle,
                            &$userId,
                            &$srcValues,
                            &$srcDbHdr,
                            &$srcDbDtl,
                            &$theResult){


  }

  /**
  * fetch the current number of sequence
  * of this sequence name
  *
  * @param  string  $COracle
  * @param  string  $thisSequence
  * @param  string  $thisPeriode
  * @param  string  $srcDb
  * @param  string  $srcJidIndex
  *
  */
  function fetchCurSequence(&$COracle,
            	              &$thisSequence,
            	              &$thisPeriode,
            	              &$srcDb,
            	              &$srcJidIndex){

    $srcPeriode = "TO_DATE('".$thisPeriode."','mm-YYYY')";

    $query  = "SELECT SEQ_NUMBER
               FROM ".$srcDb."
               WHERE  SEQ_NAME='".$thisSequence."'
                      AND PERIODE=".$srcPeriode." ";

    $COracle->execFetchQuery($query,
                             $nrows,
                             $result);

    $srcJidIndex = $result["SEQ_NUMBER"][0];
  }

  /**
  * fetch the last number of transaction
  * in this day
  *
  * @param  string    $COracle
  * @param  string    $thisDate
  * @param  string    $srcDb
  * @param  string    $lastNumber
  *
  */
  function fetchHdrNo(&$COracle,
                      &$thisDate,
                      &$srcDb,
                      &$lastNumber){

    $query  = "SELECT NO
               FROM ".$srcDb."
               WHERE  TR_DATE=TO_DATE('".$thisDate."','dd-mm-YYYY')
               ORDER BY NO DESC ";

    $COracle->execFetchQuery($query,
                             $nrows,
                             $result);

    /**
    * Return the result incremented
    *
    */
    $lastNumber = $result["NO"][0] + 1;
  }

  /**
  * fetch list of header data
  *
  * @param  string    $COracle
  * @param  string    $jobId
  * @param  string    $srcDb
  * @param  integer   $nHeader
  * @param  string    $lstHeader
  *
  */
  function fetchLstDataHeader(&$COracle,
                              &$jobId,
                               $srcDb,
                              &$nHeader,
                              &$lstHeader){

    $query  = "SELECT *
               FROM ".$srcDb."
               WHERE  JOB_ID='".$jobId."' ";

    $COracle->execFetchQuery($query,
                             $nHeader,
                             $lstHeader);
  }

  /**
  * fetch list of details data
  *
  * @param  string    $COracle
  * @param  string    $jobId
  * @param  string    $srcDb
  * @param  integer   $nDetail
  * @param  string    $lstDetail
  *
  */
  function fetchLstDataDetail( &$COracle,
                               &$jobId,
                               &$srcDb,
                               &$nDetail,
                               &$lstDetail){

    $query  = "SELECT *
               FROM ".$srcDb."
               WHERE  JOB_ID='".$jobId."'
               ORDER BY NO ASC ";

    $COracle->execFetchQuery($query,
                             $nDetail,
                             $lstDetail);
  }

  /**
  * fetch list of sub details data
  *
  * @param  string    $COracle
  * @param  string    $jobId
  * @param  string    $srcDb
  * @param  integer   $nSubDetail
  * @param  string    $lstSubDetail
  *
  */
  function fetchLstDataSubDetail(&$COracle,
                                 &$jobId,
                                 &$srcDb,
                                 &$nSubDetail,
                                 &$lstSubDetail){

    $query  = "SELECT *
               FROM ".$srcDb."
               WHERE  JOB_ID='".$jobId."'
               ORDER BY NO ASC ";

    $COracle->execFetchQuery($query,
                             $nSubDetail,
                             $lstSubDetail);
  }

  /**
  * fetch this periode sequence
  * to validate if this periode
  * isExists
  *
  * @param  string    $COracle
  * @param  string    $jobId
  * @param  string    $srcDb
  * @param  integer   $nHeader
  * @param  string    $lstHeader
  *
  */
  function fetchThisPeriodeSequence( &$COracle,
                                     &$thisPeriode,
                                     &$srcDb,
                                     &$isExists){

    $query  = "SELECT SEQ_NAME
               FROM ".$srcDb."
               WHERE  PERIODE=TO_DATE('".$thisPeriode."', 'mm-YYYY') ";

    $COracle->execFetchQuery($query,
                             $nrows,
                             $result);

    if($nrows > 0){

      $isExists = true;
    }else{

      $isExists = false;
    }
  }


  /**
  * Fetch all active sequence name by date
  *
  * @param  string    $COracle
  * @param  date      $lastPeriode
  * @param  string    $srcDb
  * @param  string    $nSequence
  * @param  integer   $lstSeqName
  */
  function fetchSeqNameByDate( &$COracle,
                               &$lastPeriode,
                               &$srcDb,
                               &$nSequence,
                               &$lstSeqName){

    $query  = "SELECT SEQ_NAME
               FROM ".$srcDb."
               WHERE  PERIODE=TO_DATE('".$lastPeriode."' ,'dd-mm-YYYY')";

    $COracle->execFetchQuery($query,
                             $nSequence,
                             $result);

    $lstSeqName = $result["SEQ_NAME"];
  }

  /**
  * Fetch last periode of sequence
  *
  * @param  string  $COracle
  * @param  string  $srcDb
  * @param  string  $lastPeriode
  *
  */
  function fetchLastPeriode( &$COracle,
                             &$srcDb,
                             &$lastPeriode){

    $query  = "SELECT PERIODE
               FROM ".$srcDb."
               ORDER BY PERIODE DESC ";

    $COracle->execFetchQuery($query,
                             $nrows,
                             $result);

    $lastPeriode = date("d-m-Y",
                        strtotime($result["PERIODE"][0]));
  }


  function fetchLastNumberByJobId(&$COracle,
                                  &$jobId,
                                  &$srcDb,
                                  &$lastNumber){

    $query  = "SELECT NO
               FROM ".$srcDb."
               WHERE  JOB_ID='".$jobId."'
               ORDER BY NO DESC ";

    $COracle->execFetchQuery($query,
                             $nrows,
                             $result);

    if($nrows > 0){

      $lastNumber = (int) $result["NO"][0] + 1;
    }else{

      $lastNumber = 1; /* Default values since there is no entry */
    }
  }

  /**
  * fetch if seguence name is exists
  *
  * @param  string  $COracle
  * @param  string  $COracle
  * @param  string  $COracle
  * @param  string  $COracle
  * @param  string  $COracle
  *
  */
  function fetchIsSeqExists( &$COracle,
                             &$index,
                             &$entSequence,
                             &$srcDb,
                             &$isExists){

    $theDate = date("d-m-Y",
                    strtotime($entSequence["PERIODE"][$index]));

    $periode = " TO_DATE('".$theDate."','dd-mm-YYYY') ";

    $query  = "SELECT *
               FROM ".$srcDb."
               WHERE  SEQ_NAME='".$entSequence["SEQ_NAME"][$index]."'
                      AND PERIODE=".$periode." ";

    $COracle->execFetchQuery($query,
                             $nrows,
                             $result);

    if($nrows > 0){

      $isExists = true;
    }else{

      $isExists = false;
    }
  }

  /**
  * Fetch last number entry of this tx
  *
  */
  function fetchLastNumEntry(&$COracle,
                             &$srcDb,
                             &$lastNumber){

    $query  = "SELECT NO
               FROM ".$srcDb."
               ORDER BY NO DESC";

    $COracle->execFetchQuery($query,
                             $nrows,
                             $result);

    if($nrows > 0){

      $lastNumber = $result["NO"][0];
    }else{

      $lastNumber = 1; /* Default start of number */
    }
  }

  /**
  * fetch level MN which reflects the menu ID
  *
  * @param  string  $COracle
  * @param  string  $srcModul
  * @param  string  $srcDb
  * @param  string  $menuId
  *
  */
  function fetchLevelMn(&$COracle,
                        &$srcModul,
                        &$srcDb,
                        &$menuId){

    $query  = "SELECT LEVELMN
               FROM ".$srcDb."
               WHERE  DESCRIPTION='".$srcModul."' ";

    $COracle->execFetchQuery($query,
                             $nrows,
                             $result);

    if($nrows > 0){

      $menuId = $result["LEVELMN"][0];
    }else{

      usrDieJs("Error: Unknown source modul ".$srcModul." ");
    }
  }

  /**
  * fetch the list of valid transactions
  *
  * @param  string    $Db
  * @param  string    $condt      the condition
  * @param  string    $srcDb
  * @param  string    $lstResult
  *
  */
  function fetchLstTransaction(&$Db,
                                $condt,
        	                     &$srcDb,
        	                     &$nResult,
        	                     &$lstResult){

    $query  = "SELECT *
               FROM ".$srcDb."
               ".$condt."
               ORDER BY TR_DATE DESC ";

    $Db->execFetchQuery($query,
                        $nResult,
                        $lstResult);
  }

}
?>